import sqlalchemy

from autotest.models.base import Base
from sqlalchemy.orm import aliased, mapped_column
from sqlalchemy import Integer, String, Text, DateTime, BigInteger, func, \
    distinct, text, and_, JSON, DECIMAL, select, update, Boolean, case

from autotest.models.system_models import User
from autotest.schemas.api.data_factory import ApiQuery, DfApiQuery,DfApiListQuery


class DataFactoryApi(Base):
    """造数场景接口信息"""
    __tablename__ = 'zo_df_api_info'

    sence_name = mapped_column(String(255), nullable=False, comment="造数场景名称", index=True)
    api_org_id = mapped_column(BigInteger, nullable=False, comment='所属域id', index=True)
    api_org_module_id = mapped_column(BigInteger, nullable=False, comment='所属子域id')
    api_type = mapped_column(Integer, comment='造数接口状态 1 post ， 2 get', default=10)
    api_status = mapped_column(Integer, comment='造数场景状态 10, 生效 ， 20 失效', default=10)
    req_url = mapped_column(String(255), nullable=False, comment='请求地址')
    req_method = mapped_column(String(20), comment='请求方式')
    req_headers = mapped_column(JSON, comment='请求头')
    req_data_moudle = mapped_column(JSON, comment='请求参数模板')
    remarks = mapped_column(String(255), comment='描述')

    @classmethod
    async def get_list(cls, params: ApiQuery):
        q = [cls.enabled_flag == 1]
        if params.id:
            q.append(cls.id == params.id)
        if params.sence_name:
            q.append(cls.sence_name.like('%{}%'.format(params.sence_name)))

        sort_type = 'asc' if params.id == 0 else 'desc'

        stmt = select(cls.id,
                      cls.sence_name).where(*q)
        print(f"stmt: {stmt}")
        return await cls.pagination(stmt)

    @classmethod
    async def get_list_by_org(cls, params: DfApiListQuery):
        q = [cls.enabled_flag == 1]
        if params.api_org_id:
            q.append(cls.api_org_id == params.api_org_id)
        sort_type = 'asc' if params.sort_type == 0 else 'desc'
        order_field = 'zo_df_api_info.id'

        # if not params.order_field or params.order_field == 'creation_date':
        #     order_field = 'zo_df_api_info.creation_date'
        # elif params.order_field == 'updation_date':
        #     order_field = 'zo_df_api_info.updation_date'
        # elif params.order_field == 'sence_name':
        #     order_field = 'zo_df_api_info.sence_name'
        # elif params.order_field == 'created_by_name' or params.order_field == 'updated_by_name':
        #     order_field = 'user.nickname'
        # else:
        #     order_field = 'zo_df_api_info.id'

        order_by = f'{order_field} {sort_type}'
        stmt = select(cls.id,
                      cls.sence_name,
                      cls.req_url,
                      cls.req_method,
                      cls.api_org_id,
                      cls.api_org_module_id,
                      cls.api_status,
                      cls.api_type,
                      cls.req_headers,
                      cls.req_data_moudle,
                      cls.updated_by,
                      cls.created_by,
                      cls.updation_date,
                      cls.creation_date,
                      cls.enabled_flag,
                      # User.nickname.label('created_by_name'),
                      # u.nickname.label('updated_by_name')).where(*q).outerjoin(User, User.id == cls.created_by).outerjoin(u, u.id == cls.updated_by).order_by(text(order_by))
                      # u.nickname.label('updated_by_name')
                      ).where(*q).order_by(text(order_by))
        print(f"stmt:{stmt}")
        return await cls.pagination(stmt)

    @classmethod
    async def get_list1(cls, params: DfApiQuery):
        q = [cls.enabled_flag == 1]
        if params.id:
            q.append(cls.id == params.id)
        if params.sence_name:
            q.append(cls.sence_name.like('%{}%'.format(params.sence_name)))
        if params.api_org_id:
            q.append(cls.api_org_id == params.api_org_id)
        if params.api_org_module_id:
            q.append(cls.api_org_module_id == params.api_org_module_id)
        if params.req_url:
            q.append(cls.req_url.like('%{}%'.format(params.req_url)))
        if params.created_by:
            q.append(cls.created_by == params.created_by)
        # if params.created_by_name:
            # q.append(User.nickname.like('%{}%'.format(params.created_by_name)))

        # u = aliased(User)
        sort_type = 'asc' if params.sort_type == 0 else 'desc'

        if not params.order_field or params.order_field == 'creation_date':
            order_field = 'zo_df_api_info.creation_date'
        elif params.order_field == 'updation_date':
            order_field = 'zo_df_api_info.updation_date'
        elif params.order_field == 'sence_name':
            order_field = 'zo_df_api_info.sence_name'
        elif params.order_field == 'created_by_name' or params.order_field == 'updated_by_name':
            order_field = 'user.nickname'
        else:
            order_field = 'zo_df_api_info.id'
        order_by = f'{order_field} {sort_type}'

        stmt = select(cls.id,
                      cls.sence_name,
                      cls.req_url,
                      cls.req_method,
                      cls.api_org_id,
                      cls.api_org_module_id,
                      cls.api_status,
                      cls.api_type,
                      cls.req_headers,
                      cls.req_data_moudle,
                      cls.updated_by,
                      cls.created_by,
                      cls.updation_date,
                      cls.creation_date,
                      cls.enabled_flag,
                      # User.nickname.label('created_by_name'),
                      # u.nickname.label('updated_by_name')).where(*q).outerjoin(User, User.id == cls.created_by).outerjoin(u, u.id == cls.updated_by).order_by(text(order_by))
                      # u.nickname.label('updated_by_name')
                      ).where(*q).order_by(text(order_by))
        print(f"stmt:{stmt}")
        return await cls.pagination(stmt)

    @classmethod
    async def get_dfapi_by_name(cls, sence_name):
        """获取用例名是否存在"""
        stmt = select(cls.get_table_columns()).where(cls.enabled_flag == 1, cls.sence_name == sence_name)
        return await cls.get_result(stmt)

    @classmethod
    async def get_api_by_id(cls, id: int):
        u = aliased(User)
        stmt = select(cls.get_table_columns(),
                      User.nickname.label('created_by_name'),
                      u.nickname.label('updated_by_name')) \
            .where(cls.id == id, cls.enabled_flag == 1) \
            .outerjoin(User, User.id == cls.created_by) \
            .outerjoin(u, u.id == cls.updated_by)
        return await cls.get_result(stmt, True)


class DataFactoryOrg(Base):
    """造数-组织信息"""
    __tablename__ = 'zo_df_organization_info'

    organization_name = mapped_column(String(20), nullable=False, comment="域名称", index=True)
    organization_type = mapped_column(Integer, comment='造数接口状态 1 主域 ， 2 子域', default=10)
    organization_module_id = mapped_column(BigInteger, nullable=False, comment='主域id')
    organization_status = mapped_column(Integer, comment='造数场景状态 10, 生效 ， 20 失效', default=10)
    remarks = mapped_column(String(255), comment='描述')

# class DataFactoryOrg(Base):
#     """造数-组织信息"""
#     __tablename__ = 'df_organization_info'
#
#     organization_name = mapped_column(String(20), nullable=False, comment="域名称", index=True)
#     organization_type = mapped_column(Integer, comment='造数接口状态 1 主域 ， 2 子域', default=10)
#     organization_module_id = mapped_column(BigInteger, nullable=False, comment='主域id')
#     organization_status = mapped_column(Integer, comment='造数场景状态 10, 生效 ， 20 失效', default=10)
#     remarks = mapped_column(String(255), comment='描述')

# if __name__ == '__main__':
#     # from celery_worker.scheduler import DatabaseScheduler
#     # db = DatabaseScheduler()
#     engine = sqlalchemy.create_engine('mysql+pymysql://root:admin123@localhost/zerorunner', echo=True)
#
#     Base.metadata.create_all(engine)


